﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XXVIII.Entity;

namespace XXVIII.DAL.SqlServerDAL
{
    public partial class SqlServerHelper
    {

        /// <summary>
        /// 获取表中最大主键ID
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private int GetTableMAXByPK(string tableName, string columnName)
        {
            int maxID = 0;
            string maxIDSql = "SELECT MAX({0}) FROM {1} ";
            maxIDSql = string.Format(maxIDSql, columnName, tableName);
            DbCommand maxIDComd = dbContext.GetSqlStringCommand(maxIDSql);
            if (dbContext.ExecuteScalar(maxIDComd) != null)
            {
                int.TryParse(dbContext.ExecuteScalar(maxIDComd).ToString(), out maxID);
            }
            return maxID;
        }

        /// <summary>
        /// 根据数据库字段类型 获取 INSERT时的 参数
        /// </summary>
        /// <param name="columnType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private string GetValueByColumnType(string columnType, string value)
        {
            if (!string.IsNullOrEmpty(columnType))
            {
                switch (columnType)
                {
                    case "int":
                        return value;
                    case "decimal":
                        return value;
                    case "float":
                        return value;
                    case "bit":
                        return value;
                    case "smallint":
                        return value;
                    case "money":
                        return value;
                    case "smallmoney":
                        return value;
                }
            }
            return "'" + value + "'";
        }

        /// <summary>
        /// 获取表字段主键
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<TablePKEntity> GetTablePK(string tableName)
        {
            List<TablePKEntity> list = new List<TablePKEntity>();
            string sql = "SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}' ";
            sql = string.Format(sql, tableName);
            DbCommand resultSqlCommand = dbContext.GetSqlStringCommand(sql);
            using (IDataReader dataReader = dbContext.ExecuteReader(resultSqlCommand))
            {
                list = DataReaderConvert.ReaderToList<TablePKEntity>(dataReader);
            }
            return list;
        }

        /// <summary>
        /// 调整 where 条件
        /// </summary>
        /// <returns></returns>
        private string TuneUpWhereSql(List<TablePropertyEntity> tableProperty, List<TableAndOrLikeEntity> andOrLikeList)
        {
            string whereSql = string.Empty;
            foreach (var item in andOrLikeList)
            {
                var exist = tableProperty.Where(p => p.ColumnName == item.ColumnName).FirstOrDefault();
                if (exist != null)
                {
                    if (item.AndOrLikeType == 1)
                    {
                        whereSql += " AND " + exist.ColumnName + "=" + this.GetValueByColumnType(exist.ColumnName, item.Value);
                    }
                    else if (item.AndOrLikeType == 2)
                    {
                        whereSql += " OR " + exist.ColumnName + "=" + this.GetValueByColumnType(exist.ColumnName, item.Value);
                    }
                    else if (item.AndOrLikeType == 3)
                    {
                        whereSql += " AND " + exist.ColumnName + " LIKE " + "'%" + item.Value + "%'";
                    }
                    else if (item.AndOrLikeType == 4)
                    {
                        whereSql += " AND " + exist.ColumnName + " LIKE " + "'%" + item.Value + "'";
                    }
                    else if (item.AndOrLikeType == 5)
                    {
                        whereSql += " AND " + exist.ColumnName + " LIKE " + "'" + item.Value + "%'";
                    }
                }
            }
            return whereSql;
        }

        /// <summary>
        /// 调整 order by条件
        /// </summary>
        /// <returns></returns>
        private string TuneUpOrderBySql(List<TablePropertyEntity> tableProperty, List<TableAndOrLikeEntity> andOrLikeList)
        {
            string orderBySql = string.Empty;
            foreach (var item in andOrLikeList)
            {
                var exist = tableProperty.Where(p => p.ColumnName == item.ColumnName).FirstOrDefault();
                if (exist != null)
                {
                   if (item.AndOrLikeType == 6)
                    {
                        orderBySql = "ORDER BY" + exist.ColumnName + " " + item.Value;
                    }
                }
            }
            return orderBySql;
        }


    }
}
